package com.samebuilding.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.samebuilding.dao.LeaveMessageReplyDAO;
import com.samebuilding.domain.LeaveMessageReply;

public class LeaveMessageReplyDAOImpl extends
		GenericDAOImpl<LeaveMessageReply, Integer> implements LeaveMessageReplyDAO {

	private static final String GET_COUNT_UNREAD_LEAVEMESSAGE = "select count(lmr.id) from LeaveMessageReply lmr, LeaveMessage lm where lmr.readed = 0 and lmr.leaveMessageId = lm.id and lm.receiveUserNo = ? and lmr.replyUserNo != ?";
	private static final String GET_COUNT_UNREAD_LEAVEMESSAGEREPLY = "select count(lmr.id) from LeaveMessageReply lmr, LeaveMessage lm where lmr.readed = 0 and lmr.leaveMessageId = lm.id and lm.leaveUserNo = ? and lmr.replyUserNo != ?";
	private static final String UPDATE_LEAVEMESSAGE_UNREAD_TO_READ = "update LeaveMessageReply lmr, LeaveMessage lm set lmr.readed = 1 where lmr.leaveMessageId = lm.id and lm.receiveUserNo = ?";
	private static final String UPDATE_LEAVEMESSAGEREPLY_UNREAD_TO_READ = "update LeaveMessageReply lmr, LeaveMessage lm set lmr.readed = 1 where lmr.leaveMessageId = lm.id and lm.leaveUserNo = ?";
	private static final String DELETE_LEAVEMESSAGEREPLY_BY_LEAVEMESSAGEID = "delete from LeaveMessageReply where leaveMessageId = ?";
	private static final String GET_REPLY_BY_LEAVEMESSAGEID = "from LeaveMessageReply where leaveMessageId = ? order by time asc";
	
	
	public int getCountUnreadLeaveMessage(final Integer receiveUserNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_UNREAD_LEAVEMESSAGE);
				query.setInteger(0, receiveUserNo);
				query.setInteger(1, receiveUserNo);
				
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public int getCountUnreadLeaveMessageReply(final Integer leaveUserNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_UNREAD_LEAVEMESSAGEREPLY);
				query.setInteger(0, leaveUserNo);
				query.setInteger(1, leaveUserNo);
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public void updateLeaveMessageReplyUnreadToRead(final Integer leaveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(UPDATE_LEAVEMESSAGEREPLY_UNREAD_TO_READ);
				query.setInteger(0, leaveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	public void updateLeaveMessageUnreadToRead(final Integer receiveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(UPDATE_LEAVEMESSAGE_UNREAD_TO_READ);
				query.setInteger(0, receiveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	public void deleteLeaveMessageReplyByLeaveMessageId(final Integer leaveMessageId) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(DELETE_LEAVEMESSAGEREPLY_BY_LEAVEMESSAGEID);
				query.setInteger(0, leaveMessageId);
				query.executeUpdate();
				return null;
			}
		});
		
	}

	@SuppressWarnings("unchecked")
	public List<LeaveMessageReply> getReplyByLeaveMessageId(final Integer leaveMessageId) {
		List<LeaveMessageReply> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_REPLY_BY_LEAVEMESSAGEID);
				query.setInteger(0, leaveMessageId);
				List<LeaveMessageReply> list = query.list();
				return list;
			}
		});
		return list;
	}

}
